const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const { generateSecureInviteCode } = require('../utils/inviteUtils');

const DB_PATH = path.join(__dirname, '../data/database.db');

// 创建数据库连接
function createConnection() {
  return new sqlite3.Database(DB_PATH, (err) => {
    if (err) {
      console.error('连接数据库失败:', err.message);
    } else {
      // console.log('已连接到 SQLite 数据库');
    }
  });
}

// 初始化数据库
async function initDatabase() {
  const db = createConnection();
  
  return new Promise((resolve, reject) => {
    // 创建用户表
    const createUsersTable = `
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username VARCHAR(50) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        email VARCHAR(100),
        is_admin BOOLEAN DEFAULT 0,
        can_invite BOOLEAN DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `;

    // 创建流量配置表
    const createTrafficConfigsTable = `
      CREATE TABLE IF NOT EXISTS traffic_configs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        token VARCHAR(500) NOT NULL,
        phone VARCHAR(20) NOT NULL,
        operator VARCHAR(20) DEFAULT '联通',
        app_id VARCHAR(100),
        monitor_url VARCHAR(500),
        monitor_token VARCHAR(64),
        is_active BOOLEAN DEFAULT 1,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users (id)
      )
    `;

    // 创建监控链接表
    const createMonitorLinksTable = `
      CREATE TABLE IF NOT EXISTS monitor_links (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        config_id INTEGER NOT NULL,
        monitor_url VARCHAR(500) NOT NULL,
        monitor_token VARCHAR(64) UNIQUE NOT NULL,
        is_active BOOLEAN DEFAULT 1,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (config_id) REFERENCES traffic_configs (id)
      )
    `;

    // 创建流量详情表
    const createTrafficDetailsTable = `
      CREATE TABLE IF NOT EXISTS traffic_details (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        config_id INTEGER NOT NULL,
        total_traffic REAL DEFAULT 0,
        used_traffic REAL DEFAULT 0,
        remaining_traffic REAL DEFAULT 0,
        general_used REAL DEFAULT 0,
        general_remaining REAL DEFAULT 0,
        directed_used REAL DEFAULT 0,
        directed_remaining REAL DEFAULT 0,
        jump_points INTEGER DEFAULT 0,
        freeflow REAL DEFAULT 0,
        package_name VARCHAR(100),
        query_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (config_id) REFERENCES traffic_configs (id)
      )
    `;

    // 创建邀请码表
    const createInviteCodesTable = `
      CREATE TABLE IF NOT EXISTS invite_codes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        code VARCHAR(20) UNIQUE NOT NULL,
        created_by INTEGER,
        used_by INTEGER,
        is_used BOOLEAN DEFAULT 0,
        is_active BOOLEAN DEFAULT 1,
        expires_at DATETIME,
        max_uses INTEGER DEFAULT 1,
        use_count INTEGER DEFAULT 0,
        invite_type VARCHAR(20) DEFAULT 'INVITE',
        description TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        used_at DATETIME,
        FOREIGN KEY (created_by) REFERENCES users (id),
        FOREIGN KEY (used_by) REFERENCES users (id)
      )
    `;

    // 创建系统配置表
    const createSystemConfigTable = `
      CREATE TABLE IF NOT EXISTS system_config (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        key VARCHAR(50) UNIQUE NOT NULL,
        value TEXT NOT NULL,
        description TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `;

    // 执行建表语句
    db.serialize(() => {
      db.run(createUsersTable, (err) => {
        if (err) {
          console.error('创建用户表失败:', err.message);
          reject(err);
          return;
        }
        console.log('用户表创建成功');
      });

      db.run(createTrafficConfigsTable, (err) => {
        if (err) {
          console.error('创建流量配置表失败:', err.message);
          reject(err);
          return;
        }
        console.log('流量配置表创建成功');
      });

      db.run(createMonitorLinksTable, (err) => {
        if (err) {
          console.error('创建监控链接表失败:', err.message);
          reject(err);
          return;
        }
        console.log('监控链接表创建成功');
      });

      db.run(createTrafficDetailsTable, (err) => {
          if (err) {
            console.error('创建流量详情表失败:', err.message);
            reject(err);
            return;
          }
          console.log('流量详情表创建成功');
          
          // 创建邀请码表
          db.run(createInviteCodesTable, (err) => {
            if (err) {
              console.error('创建邀请码表失败:', err.message);
              reject(err);
              return;
            }
            console.log('邀请码表创建成功');
            
            // 创建系统配置表
            db.run(createSystemConfigTable, (err) => {
              if (err) {
                console.error('创建系统配置表失败:', err.message);
                reject(err);
                return;
              }
              console.log('系统配置表创建成功');
              
              // 检查并添加monitor_token字段（迁移）
              db.run(`ALTER TABLE traffic_configs ADD COLUMN monitor_token VARCHAR(64)`, (err) => {
                if (err && !err.message.includes('duplicate column name')) {
                  console.error('添加monitor_token字段失败:', err.message);
                } else if (!err) {
                  console.log('monitor_token字段添加成功');
                }
                
                // 检查并添加package_name字段（迁移）
                db.run(`ALTER TABLE traffic_details ADD COLUMN package_name VARCHAR(100)`, (err) => {
                  if (err && !err.message.includes('duplicate column name')) {
                    console.error('添加package_name字段失败:', err.message);
                  } else if (!err) {
                    console.log('package_name字段添加成功');
                  }
                  
                  // 检查并添加freeflow字段（迁移）
                  db.run(`ALTER TABLE traffic_details ADD COLUMN freeflow REAL DEFAULT 0`, (err) => {
                    if (err && !err.message.includes('duplicate column name')) {
                      console.error('添加freeflow字段失败:', err.message);
                    } else if (!err) {
                      console.log('freeflow字段添加成功');
                    }
                    
                    // 检查并添加用户权限字段（迁移）
                    db.run(`ALTER TABLE users ADD COLUMN is_admin BOOLEAN DEFAULT 0`, (err) => {
                      if (err && !err.message.includes('duplicate column name')) {
                        console.error('添加is_admin字段失败:', err.message);
                      } else if (!err) {
                        console.log('is_admin字段添加成功');
                      }
                      
                      db.run(`ALTER TABLE users ADD COLUMN can_invite BOOLEAN DEFAULT 0`, (err) => {
                        if (err && !err.message.includes('duplicate column name')) {
                          console.error('添加can_invite字段失败:', err.message);
                        } else if (!err) {
                          console.log('can_invite字段添加成功');
                        }
                        
                        // 添加邀请码表的新字段
                        const inviteCodeMigrations = [
                          { sql: 'ALTER TABLE invite_codes ADD COLUMN expires_at DATETIME', name: 'expires_at' },
                          { sql: 'ALTER TABLE invite_codes ADD COLUMN max_uses INTEGER DEFAULT 1', name: 'max_uses' },
                          { sql: 'ALTER TABLE invite_codes ADD COLUMN use_count INTEGER DEFAULT 0', name: 'use_count' },
                          { sql: 'ALTER TABLE invite_codes ADD COLUMN invite_type VARCHAR(20) DEFAULT \'INVITE\'', name: 'invite_type' },
                          { sql: 'ALTER TABLE invite_codes ADD COLUMN description TEXT', name: 'description' }
                        ];
                        
                        let migrationIndex = 0;
                        
                        const runNextMigration = () => {
                          if (migrationIndex >= inviteCodeMigrations.length) {
                            // 所有迁移完成，创建索引
                            createInviteCodeIndexes(db, resolve, reject);
                            return;
                          }
                          
                          const migration = inviteCodeMigrations[migrationIndex];
                          db.run(migration.sql, (err) => {
                            if (err && !err.message.includes('duplicate column name')) {
                              console.error(`添加${migration.name}字段失败:`, err.message);
                            } else if (!err) {
                              console.log(`${migration.name}字段添加成功`);
                            }
                            
                            migrationIndex++;
                            runNextMigration();
                          });
                        };
                        
                        // 添加新字段的迁移
                        const newFieldMigrations = [
                          { sql: 'ALTER TABLE traffic_details ADD COLUMN detailed_data TEXT', name: 'detailed_data' },
                          { sql: 'ALTER TABLE traffic_details ADD COLUMN data_source VARCHAR(20) DEFAULT \'legacy\'', name: 'data_source' }
                        ];
                        
                        let newFieldIndex = 0;
                        
                        const runNewFieldMigration = () => {
                          if (newFieldIndex >= newFieldMigrations.length) {
                            runNextMigration();
                            return;
                          }
                          
                          const migration = newFieldMigrations[newFieldIndex];
                          db.run(migration.sql, (err) => {
                            if (err && !err.message.includes('duplicate column name')) {
                              console.error(`添加${migration.name}字段失败:`, err.message);
                            } else if (!err) {
                              console.log(`${migration.name}字段添加成功`);
                            }
                            
                            newFieldIndex++;
                            runNewFieldMigration();
                          });
                        };
                        
                        runNewFieldMigration();
                      });
                    });
                  });
                });
              });
            });
          });
        });
    });
  });
}

// 创建邀请码表索引
function createInviteCodeIndexes(db, resolve, reject) {
  const indexes = [
    'CREATE INDEX IF NOT EXISTS idx_invite_codes_code ON invite_codes(code)',
    'CREATE INDEX IF NOT EXISTS idx_invite_codes_created_by ON invite_codes(created_by)',
    'CREATE INDEX IF NOT EXISTS idx_invite_codes_used_by ON invite_codes(used_by)',
    'CREATE INDEX IF NOT EXISTS idx_invite_codes_is_used ON invite_codes(is_used)',
    'CREATE INDEX IF NOT EXISTS idx_invite_codes_is_active ON invite_codes(is_active)',
    'CREATE INDEX IF NOT EXISTS idx_invite_codes_expires_at ON invite_codes(expires_at)',
    'CREATE INDEX IF NOT EXISTS idx_invite_codes_invite_type ON invite_codes(invite_type)',
    'CREATE INDEX IF NOT EXISTS idx_invite_codes_created_at ON invite_codes(created_at)'
  ];
  
  let indexCount = 0;
  
  indexes.forEach((indexSql, index) => {
    db.run(indexSql, (err) => {
      if (err) {
        console.error(`创建索引失败:`, err.message);
      } else {
        console.log(`索引创建成功: ${index + 1}/${indexes.length}`);
      }
      
      indexCount++;
      if (indexCount === indexes.length) {
        // 所有索引创建完成，初始化系统数据
        initializeSystemData(db, resolve, reject);
      }
    });
  });
}

// 初始化系统数据
function initializeSystemData(db, resolve, reject) {
  // 检查是否已有邀请码
  db.get('SELECT COUNT(*) as count FROM invite_codes', (err, row) => {
    if (err) {
      console.error('检查邀请码失败:', err.message);
      db.close();
      reject(err);
      return;
    }
    
    // 如果没有邀请码，生成初始邀请码
    if (row.count === 0) {
      const initialInviteCode = generateSecureInviteCode(8);
      
      db.run('INSERT INTO invite_codes (code, created_by) VALUES (?, ?)', [initialInviteCode, null], (err) => {
        if (err) {
          console.error('创建初始邀请码失败:', err.message);
          db.close();
          reject(err);
          return;
        }
        
        console.log(`初始邀请码已生成: ${initialInviteCode}`);
        
        // 设置系统配置
        db.run('INSERT OR REPLACE INTO system_config (key, value, description) VALUES (?, ?, ?)', 
               ['allow_all_invite', 'false', '是否允许所有用户邀请新用户'], (err) => {
          if (err) {
            console.error('设置系统配置失败:', err.message);
            db.close();
            reject(err);
            return;
          }
          
          console.log('系统配置初始化完成');
          db.close();
          resolve();
        });
      });
    } else {
      // 已有邀请码，直接完成初始化
      console.log('邀请码已存在，跳过初始化');
      db.close();
      resolve();
    }
  });
}

module.exports = {
  createConnection,
  initDatabase,
  DB_PATH
};
